

--////////////////////////////////////////////DAT HANG/////////////////////////////////////////
CREATE PROCEDURE sp_InsertPhieuGiao
	@Ma_PG nchar(3),
	@Ma_PD nchar(3),
	@NV_Giao nchar(3),
	@NgayGiao datetime,
	@ThanhTien  float,
	@TinhTrang  int,
	@GhiChu nvarchar(100)
AS
BEGIN
BEGIN TRAN
	INSERT INTO PhieuGiao  VALUES(@Ma_PG,@Ma_PD,@NV_Giao,@NgayGiao,@ThanhTien,@TinhTrang,@GhiChu)
COMMIT TRAN
END
GO

--EXEC sp_InsertPhieuGiao '011','005','005','2013-05-09 00:00:00.000',236000,1,N''

CREATE PROCEDURE sp_UpdatePhieuGiaoById
	@Ma_PG nchar(3),
	@Ma_PD nchar(3),
	@NV_Giao nchar(3),
	@NgayGiao datetime,
	@ThanhTien  float,
	@TinhTrang  int,
	@GhiChu nvarchar(100)
AS
BEGIN
BEGIN TRAN
	UPDATE  PhieuGiao SET Ma_PD=Ma_PD,NV_Giao=@NV_Giao,NgayGiao=@NgayGiao,ThanhTien=@ThanhTien,TinhTrang=@TinhTrang,Ghi_Chu=@GhiChu WHERE Ma_PG=@Ma_PG
COMMIT TRAN
END
GO

--EXEC sp_UpdatePhieuDatById '011','005',N'Lê Văn Lách Luât',N'9999999 Hai Bà Trưng','37477755','2013-05-09 00:00:00.000',236000,1,N'Ưho knớ' 

CREATE PROCEDURE sp_DeletePhieuGiaoById
	@Ma_PG nchar(3)
AS
BEGIN
BEGIN TRAN
	DELETE FROM PhieuGiao WHERE Ma_PG=@Ma_PG
COMMIT TRAN
END
GO

--EXEC sp_DeletePhieuDatById '011'

CREATE PROCEDURE sp_SelectPhieuGiaoAll
AS
BEGIN
BEGIN TRAN
	SELECT  Ma_PG,Ma_PD,NV_Giao,NgayGiao,ThanhTien,TinhTrang,Ghi_Chu FROM PhieuGiao
COMMIT TRAN
END
GO
--EXEC sp_SelectPhieuDat

CREATE PROCEDURE sp_SelectPhieuGiaoById
	@Ma_PG nchar(3)
AS
BEGIN
BEGIN TRAN
	SELECT  Ma_PD,NV_Giao,NgayGiao,ThanhTien,TinhTrang,Ghi_Chu FROM PhieuGiao WHERE Ma_PG=@Ma_PG
COMMIT TRAN
END
GO
--EXEC sp_SelectPhieuDatById'002'

CREATE PROCEDURE sp_CreatePhieuGiaoId
AS
BEGIN
		DECLARE @max NCHAR(3)
		SELECT @max = max(Ma_PG) FROM PhieuGiao
		IF(@max is NULL)
			SELECT '001'
		ELSE
			BEGIN
			SET @max =  cast(@max AS INT)+1
			SELECT cast(@max AS NCHAR(3))
			END
END
GO
--EXEC sp_CreatePhieuDatId


--/////////////////////////////////////////////////////////////CHI TIET PHIEU DAT////////////////////////////////////////////
CREATE PROCEDURE sp_InsertChiTietPhieuGiao
	@MaChiTietPG nchar(3),
	@Ma_PG nchar(3),
	@Ma_Mon nchar(3),
	@SoLuong int,
	@DonGia float
AS
BEGIN
BEGIN TRAN
	DECLARE @SoLuonghop int
	SELECT @SoLuonghop=Soluong FROM MonAn_Hop WHERE @Ma_Mon=Ma_Mon
	Set @SoLuonghop=@SoLuonghop*@SoLuong
	
	INSERT INTO ChiTietPhieuGiao VALUES(@MaChiTietPG,@Ma_PG,@Ma_Mon,@SoLuong,@DonGia,@SoLuonghop)


COMMIT TRAN
END
GO

--EXEC sp_InsertChiTietPhieuGiao'023','011','013',5,20000

CREATE PROCEDURE sp_UpdateChiTietPhieuGiaoById
	@MaChiTietPG nchar(3),
	@Ma_PG nchar(3),
	@Ma_Mon nchar(3),
	@SoLuong int,
	@DonGia float,
	@soHop int
AS
BEGIN
BEGIN TRAN
	Update ChiTietPhieuGiao SET Ma_PG=@Ma_PG,Ma_Mon=@Ma_Mon,SoLuong=@SoLuong,DonGia=@DonGia,SoHop=@soHop WHERE MaChiTietPG=@MaChiTietPG
COMMIT TRAN
END
GO

--EXEC sp_UpdateChiTietPhieuDatById'023','010','013',5,2700000,135000,null

CREATE PROCEDURE sp_DeleteChiTietPhieuGiaoById
	@MaChiTietPG nchar(3)
AS
BEGIN
BEGIN TRAN
	DELETE FROM ChiTietPhieuGiao WHERE MaChiTietPG=@MaChiTietPG
COMMIT TRAN
END
GO

--EXEC sp_DeleteChiTietPhieuDatById '023'


CREATE PROCEDURE sp_SelectChiTietPhieuGiaoAll
AS
BEGIN
BEGIN TRAN
	SELECT MaChiTietPG,Ma_PG,Ma_Mon,SoLuong,DonGia,SoHop FROM ChiTietPhieuGiao
COMMIT TRAN
END
GO

--EXEC sp_SelectChiTietPhieuGiaoAll

CREATE PROCEDURE sp_SelectChiTietPhieuDatById
	@MaChiTietPG nchar(3)
AS
BEGIN
BEGIN TRAN
	SELECT Ma_PG,Ma_Mon,SoLuong,DonGia,SoHop FROM ChiTietPhieuGiao WHERE MaChiTietPG=@MaChiTietPG
COMMIT TRAN
END
GO

--EXEC sp_SelectChiTietPhieuDatById '022'



CREATE  PROCEDURE sp_CreateChiTietPhieuGiaoById
AS
BEGIN
		DECLARE @max NCHAR(3)
		SELECT @max = max(MaChiTietPG) FROM ChiTietPhieuGiao
		IF(@max is NULL)
			SELECT '001'
		ELSE
			BEGIN
			SET @max =  cast(@max AS INT)+1
			SELECT cast(@max AS NCHAR(3))
			END
END
GO

--EXEC sp_CreateChiTietPhieuDatId